import pymysql
from typing import List, Tuple, Optional

class DatabaseManager:
    def __init__(self):
        self.connection = None
        self.connect()
    
    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = pymysql.connect(
                host = "gateway01.ap-southeast-1.prod.aws.tidbcloud.com",
                port = 4000,
                user = "2eoagz2rTEiMGMb.root",
                password = "R0T40GnEid6BBdaG",
                database = "my_db",
                ssl_verify_cert = True,
                ssl_verify_identity = True,
                ssl_ca = "/etc/ssl/certs/ca-certificates.crt"
            )
            print("数据库连接成功！")
        except Exception as e:
            print(f"连接失败: {str(e)}")
            raise e

    def add_record(self, name: str, message: str) -> bool:
        """添加记录"""
        try:
            with self.connection.cursor() as cursor:
                sql = "INSERT INTO test_table (name, message) VALUES (%s, %s)"
                cursor.execute(sql, (name, message))
                self.connection.commit()
                print(f"成功添加记录: {name}, {message}")
                return True
        except Exception as e:
            print(f"添加记录失败: {str(e)}")
            self.connection.rollback()
            return False

    def get_all_records(self) -> List[Tuple]:
        """获取所有记录"""
        try:
            with self.connection.cursor() as cursor:
                sql = "SELECT * FROM test_table"
                cursor.execute(sql)
                results = cursor.fetchall()
                return results
        except Exception as e:
            print(f"获取记录失败: {str(e)}")
            return []

    def get_record_by_id(self, id: int) -> Optional[Tuple]:
        """根据ID获取单条记录"""
        try:
            with self.connection.cursor() as cursor:
                sql = "SELECT * FROM test_table WHERE id = %s"
                cursor.execute(sql, (id,))
                return cursor.fetchone()
        except Exception as e:
            print(f"获取记录失败: {str(e)}")
            return None

    def update_record(self, id: int, name: str = None, message: str = None) -> bool:
        """更新记录"""
        try:
            with self.connection.cursor() as cursor:
                updates = []
                params = []
                if name is not None:
                    updates.append("name = %s")
                    params.append(name)
                if message is not None:
                    updates.append("message = %s")
                    params.append(message)
                
                if not updates:
                    return False
                
                sql = f"UPDATE test_table SET {', '.join(updates)} WHERE id = %s"
                params.append(id)
                cursor.execute(sql, tuple(params))
                self.connection.commit()
                print(f"成功更新ID为{id}的记录")
                return True
        except Exception as e:
            print(f"更新记录失败: {str(e)}")
            self.connection.rollback()
            return False

    def delete_record(self, id: int) -> bool:
        """删除记录"""
        try:
            with self.connection.cursor() as cursor:
                sql = "DELETE FROM test_table WHERE id = %s"
                cursor.execute(sql, (id,))
                self.connection.commit()
                print(f"成功删除ID为{id}的记录")
                return True
        except Exception as e:
            print(f"删除记录失败: {str(e)}")
            self.connection.rollback()
            return False

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print("数据库连接已关闭")

def main():
    # 使用示例
    db = DatabaseManager()
    try:
        # 添加记录
        db.add_record("张三", "Hello!")
        db.add_record("李四", "Hi there!")
        
        # 获取所有记录
        print("\n所有记录：")
        records = db.get_all_records()
        for record in records:
            print(f"ID: {record[0]}, 名称: {record[1]}, 消息: {record[2]}")
        
        # 获取单条记录
        record_id = 1
        record = db.get_record_by_id(record_id)
        if record:
            print(f"\n获取ID为{record_id}的记录：")
            print(f"ID: {record[0]}, 名称: {record[1]}, 消息: {record[2]}")
        
        # 更新记录
        db.update_record(1, name="张三(已更新)", message="Updated message!")
        
        # 删除记录
        db.delete_record(2)
        
        # 再次获取所有记录
        print("\n更新后的所有记录：")
        records = db.get_all_records()
        for record in records:
            print(f"ID: {record[0]}, 名称: {record[1]}, 消息: {record[2]}")
            
    finally:
        db.close()

if __name__ == "__main__":
    main()
